using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;


public partial class StoredProcedures
{
    [Microsoft.SqlServer.Server.SqlProcedure]
    public static void SelectManyModule_BarItem(SqlGuid SessionGuid, SqlGuid RibbonBarGuid, SqlGuid ParentBarItemGuid)
    {
        using (SqlConnection connection = new SqlConnection("context connection=true"))
        {
            try
            {
                connection.Open();

                string selectManyModuleBarItemCommandText =
                @"
                    SELECT 
                        BarItemGuid, BarItemId, RibbonBarGuid, ParentBarItemGuid, CommandGuid, BarItemName, BarItemImage, BarItemImageSize, BarItemTypeId, BarItemOrder 

                    FROM 
                        Module_BarItem

                    WHERE 
                        RibbonBarGuid IN 
                        (
                            SELECT
                                SRBR.RibbonBarGuid

                            FROM 
                                Security_Session SS

                            INNER JOIN
                                Security_UserRole SUR
                                ON SS.UserGuid = SUR.UserGuid

                            INNER JOIN
                                Security_RibbonBarRole SRBR
                                ON SUR.RoleGuid = SRBR.RoleGuid

                            WHERE 
                                SessionGuid = @SessionGuid
                        )

                    AND
	                    ((@ParentBarItemGuid IS NULL) OR (ParentBarItemGuid = @ParentBarItemGuid))

                    AND
	                    RibbonBarGuid = @RibbonBarGuid

                    ORDER BY 
                        BarItemOrder ASC
                ";

                SqlCommand selectManyModuleBarItemCommand = new SqlCommand(selectManyModuleBarItemCommandText, connection);

                selectManyModuleBarItemCommand.Parameters.Add(new SqlParameter("@SessionGuid", SessionGuid));
                selectManyModuleBarItemCommand.Parameters.Add(new SqlParameter("@ParentBarItemGuid", ParentBarItemGuid));
                selectManyModuleBarItemCommand.Parameters.Add(new SqlParameter("@RibbonBarGuid", RibbonBarGuid));

                SqlContext.Pipe.ExecuteAndSend(selectManyModuleBarItemCommand);
            }
            catch (System.Exception exception)
            {
                SqlContext.Pipe.Send("An exception occured");
                SqlContext.Pipe.Send("Message: " + exception.Message);
                SqlContext.Pipe.Send("Source: " + exception.Source);
                SqlContext.Pipe.Send("Stack: " + exception.StackTrace);
                SqlContext.Pipe.Send("Target: " + exception.TargetSite);
            }
            finally
            {
                connection.Close();
            }
        }
    }
};
